package org.lq.recruitstudent.dao.impl;

import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.recruitstudent.dao.StudentInfoDao;
import org.lq.recruitstudent.entity.StudentInfo;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 黑猫警长
 * @create 2020-10-13
 */
@Log4j
public class StudentInfoDaoImpl implements StudentInfoDao {

    /**
     * t添加
     *
     * @param studentInfo
     * @return
     */
    @Override
    public int save(StudentInfo studentInfo) {
        log.info("数据访问层==[StudentInfoDaoImpl]->save(StudentInfo studentInfo),开始执行");
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = qr.update("insert into student_info(staff_id,class_id,student_name," +
                    "student_sex,student_age,student_tellphone,student_email,student_idcard," +
                    "student_address,student_birthday,student_school,student_qq," +
                    "student_parents_name,student_parents_phone,student_pro,student_pro_city," +
                    "student_type,student_ispay,student_sate,student_mark,student_desc," +
                    "student_number,student_password)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    studentInfo.getStaffId(),
                    studentInfo.getClassId(),studentInfo.getStudentName(),studentInfo.getStudentSex(),
                    studentInfo.getStudentAge(),studentInfo.getStudentTellphone(),
                    studentInfo.getStudentEmail(),studentInfo.getStudentIdcard(),
                    studentInfo.getStudentAddress(),studentInfo.getStudentBirthday(),
                    studentInfo.getStudentSchool(),studentInfo.getStudentQq(),
                    studentInfo.getStudentParentsName(),studentInfo.getStudentParentsPhone(),
                    studentInfo.getStudentPro(),studentInfo.getStudentProCity(),
                    studentInfo.getStudentType(),studentInfo.getStudentIspay(),
                    studentInfo.getStudentSate(),studentInfo.getStudentMark(),
                    studentInfo.getStudentDesc(),studentInfo.getStudentNumber(),
                    studentInfo.getStudentPassword());
        } catch (SQLException e) {
            log.error("添加数据出现异常",e);
        }
        log.info("返回结果"+num);
        log.info("数据访问层==[StudentInfoDaoImpl]->save(StudentInfo studentInfo),执行结束");
        return num;
    }

    /**
     * 修改
     *
     * @param studentInfo
     * @return
     */
    @Override
    public int update(StudentInfo studentInfo) {
        log.info("数据访问层==[StudentInfoDaoImpl]->update(StudentInfo studentInfo),开始执行");
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = qr.update("update student_info set staff_id=?,class_id=?,student_name=?," +
                            "student_sex=?,student_age=?,student_tellphone=?,student_email=?,student_idcard=?," +
                            "student_address=?,student_birthday=?,student_school=?,student_qq=?," +
                            "student_parents_name=?,student_parents_phone=?,student_pro=?,student_pro_city=?," +
                            "student_type=?,student_ispay=?,student_sate=?,student_mark=?,student_desc=?," +
                            "student_number=?,student_password=? where student_id=?",
                    studentInfo.getStaffId(),
                    studentInfo.getClassId(),studentInfo.getStudentName(),studentInfo.getStudentSex(),
                    studentInfo.getStudentAge(),studentInfo.getStudentTellphone(),
                    studentInfo.getStudentEmail(),studentInfo.getStudentIdcard(),
                    studentInfo.getStudentAddress(),studentInfo.getStudentBirthday(),
                    studentInfo.getStudentSchool(),studentInfo.getStudentQq(),
                    studentInfo.getStudentParentsName(),studentInfo.getStudentParentsPhone(),
                    studentInfo.getStudentPro(),studentInfo.getStudentProCity(),
                    studentInfo.getStudentType(),studentInfo.getStudentIspay(),
                    studentInfo.getStudentSate(),studentInfo.getStudentMark(),
                    studentInfo.getStudentDesc(),studentInfo.getStudentNumber(),
                    studentInfo.getStudentPassword(),studentInfo.getStudentId());
        } catch (SQLException e) {
            log.error("修改数据出现异常",e);
        }
        log.info("返回结果"+num);
        log.info("数据访问层==[StudentInfoDaoImpl]-update(StudentInfo studentInfo),执行结束");
        return num;
    }

    /**
     * 删除
     *
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        log.info("数据访问层==[StudentInfoDaoImpl]->delete(int id),开始执行");
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = qr.update("delete from student_info where student_id=?",id);
        } catch (SQLException e) {
            log.error("删除数据出现异常",e);
        }
        log.info("返回结果"+num);
        log.info("数据访问层==[StudentInfoDaoImpl]->delete(int id),执行结束");
        return num;
    }

    /**
     * 通过ID查询
     *
     * @param id
     * @return
     */
    @Override
    public StudentInfo getById(int id) {
        log.info("数据访问层==[StudentInfoDaoImpl]->getById(int id),开始执行");
        StudentInfo studentInfo = new StudentInfo();
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            studentInfo = qr.query("select * from v_student_info where studentId=?",
                    new BeanHandler<StudentInfo>(StudentInfo.class),id);
        } catch (SQLException e) {
            log.error("查询数据出现异常",e);
        }
        log.info("返回结果"+studentInfo);
        log.info("数据访问层==[StudentInfoDaoImpl]->getById(int id),执行结束");
        return studentInfo;
    }

    /**
     * 总行数
     *
     * @return
     */
    @Override
    public int getCount() {
        log.info("数据访问层==[StudentInfoDaoImpl]->getCount(),开始执行");
        int count = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            Long num = qr.query("select count(1) from v_student_info",new ScalarHandler<>());
            count = num.intValue();
        } catch (SQLException e) {
            log.error("查询数据出现异常",e);
        }
        log.info("返回结果"+count);
        log.info("数据访问层==[StudentInfoDaoImpl]->getCount(),执行结束");
        return count;
    }

    /**
     * 分页查询
     *
     * @param startIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<StudentInfo> pageList(int startIndex, int pageSize) {
        log.info("数据访问层==[StudentInfoDaoImpl]->pageList(int startIndex, int pageSize),开始执行");
        List<StudentInfo> list = new ArrayList<>();
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = qr.query("select * from v_student_info limit ?,?",
                    new BeanListHandler<StudentInfo>(StudentInfo.class),
                    startIndex,pageSize);
        } catch (SQLException e) {
            log.error("查询数据出现异常",e);
        }
        log.info("返回结果"+list);
        log.info("数据访问层==[StudentInfoDaoImpl]->pageList(int startIndex, int pageSize),执行结束");
        return list;
    }

    @Override
    public int getCount(String... values) {
        return 0;
    }

    @Override
    public List<StudentInfo> pageByValues(int startIndex, int pageSize, String... value) {
        return null;
    }

    /**
     * 根据条件查询总行数
     * @param name
     * @param state
     * @return
     */
    @Override
    public int getCount(String name,int state) {
        log.info("数据访问层==[StudentInfoDaoImpl]->getCount(String name,int state),开始执行");
        int count = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            Long num = qr.query("select count(1) from v_student_info where studentName like ? and studentSate=?",new ScalarHandler<>(),"%"+name+"%",state);
            count = num.intValue();
        } catch (SQLException e) {
            log.error("查询数据出现异常",e);
        }
        log.info("返回结果"+count);
        log.info("数据访问层==[StudentInfoDaoImpl]->getCount(String name,int state),执行结束");
        return count;
    }

    /**
     * 根据条件模糊分页查询
     * @param startIndex
     * @param pageSize
     * @param name
     * @param state
     * @return
     */
    @Override
    public List<StudentInfo> pageByValues(int startIndex, int pageSize,String name,int state) {
        log.info("数据访问层==[StudentInfoDaoImpl]->pageByValues(int startIndex, int pageSize,String name,int state),开始执行");
        List<StudentInfo> list = new ArrayList<>();
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = qr.query("select * from v_student_info where studentName like ? and studentSate=? limit ?,?",
                    new BeanListHandler<StudentInfo>(StudentInfo.class),
                    "%"+name+"%",state,startIndex,pageSize);
        } catch (SQLException e) {
            log.error("查询数据出现异常",e);
        }
        log.info("返回结果"+list);
        log.info("数据访问层==[StudentInfoDaoImpl]->pageByValues(int startIndex, int pageSize,String name,int state),执行结束");
        return list;
    }

    /**
     * 根据条件查询总行数
     *
     * @param name
     * @return
     */
    @Override
    public int getCount(String name) {
        log.info("数据访问层==[StudentInfoDaoImpl]->getCount(String name),开始执行");
        int count = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            Long num = qr.query("select count(1) from v_student_info where studentName like ?",new ScalarHandler<>(),"%"+name+"%");
            count = num.intValue();
        } catch (SQLException e) {
            log.error("查询数据出现异常",e);
        }
        log.info("返回结果"+count);
        log.info("数据访问层==[StudentInfoDaoImpl]->getCount(String name),执行结束");
        return count;
    }

    /**
     * 根据名字模糊查询
     *
     * @param startIndex
     * @param pageSize
     * @param name
     * @return
     */
    @Override
    public List<StudentInfo> findByName(int startIndex, int pageSize, String name) {
        log.info("数据访问层==[StudentInfoDaoImpl]->findByName(int startIndex, int pageSize, String name),开始执行");
        List<StudentInfo> list = new ArrayList<>();
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = qr.query("select * from v_student_info where studentName like ? limit ?,?",
                    new BeanListHandler<StudentInfo>(StudentInfo.class),
                    "%"+name+"%",startIndex,pageSize);
        } catch (SQLException e) {
            log.error("查询数据出现异常",e);
        }
        log.info("返回结果"+list);
        log.info("数据访问层==[StudentInfoDaoImpl]->findByName(int startIndex, int pageSize, String name),执行结束");
        return list;
    }
}
